-- @owner: @wang-tianjing1
-- @date: 2022-03-23
-- @testpoint: 验证connect by语句结合where条件

--step1: 创建查询表 expect: 创建成功
drop table if exists t_connect_0052_01;
drop table if exists t_connect_0052_02;
create table t_connect_0052_01 (id1 int , col1 text);
create table t_connect_0052_02 (id2 int , col2 int);

--step2: 查询表插入数据 expect: 成功
drop procedure if exists p_insert_0052();
create or replace procedure p_insert_0052()
as
insert_str varchar;
begin
    for i in 1..10 loop
        insert_str = 'insert into t_connect_0052_01 values('||i||','||''''||'text_'||i||''''||')';
        execute immediate insert_str;
    end loop;
return;
end;
/

call p_insert_0052();
insert into t_connect_0052_02 values (generate_series(1,10),random()*100);

--step3: connect by语句结合where条件; expect: 查询成功
select count(*) from t_connect_0052_01,t_connect_0052_02 where id1 > 5 connect by prior col2 = id1;

--step4: 清理环境 expect: 成功
drop procedure p_insert_0052();
drop table if exists t_connect_0052_01;
drop table if exists t_connect_0052_02;
